﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using InformationSystem.Entity;

namespace InformationSystem.Dao
{
	/// <summary>
	/// Description of ModuleDao.
	/// </summary>
	public class ModuleDao : BaseDao, IDao<Module>
	{
		public ModuleDao()
		{
		}

		#region IDao implementation

		public bool add(Module module)
		{
			String sql = "insert into t_module ( name, status, credit, addDate) values"
                                             + "(@name,@status,@credit,datetime('now', 'localtime'))";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",module.name.ToString()),
				helper.createSqliteParam("@status",module.status.ToString()),
				helper.createSqliteParam("@credit",module.credit.ToString())
			};

            return helper.Exc(sql, param) == 1;
		}

		public bool del(Module module)
		{
			String sql = "delete from t_module where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",module.id.ToString())
			};

            return helper.Exc(sql, param) == 1;
		}

		public bool update(Module module)
		{
			String sql = "update t_module  set name=@name, status=@status, credit=@credit where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",module.name.ToString()),
				helper.createSqliteParam("@status",module.status.ToString()),
				helper.createSqliteParam("@credit",module.credit.ToString()),
                helper.createSqliteParam("@id",module.id.ToString())
			};

            return helper.Exc(sql, param) == 1;
		}

		public Module query(Module module)
		{
			String sql = "select id, name, status, credit, addDate from t_module where id = @id";
			SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",module.id.ToString())
			};
			
			SQLiteDataReader sdr = helper.Read(sql,param);
			
			Module retModule = new Module();
			
			while (sdr.Read()) 
			{
				retModule.id = sdr.GetInt32(0);
				retModule.name = sdr.GetString(1);
				retModule.status = sdr.GetInt32(2);
				retModule.credit = sdr.GetInt32(3);
				retModule.addDate = sdr.GetString(4);
				
			}
			
			sdr.Close();
			sdr.Dispose();
			
			
			return retModule;
		}

		public List<Module> queryAll()
		{
			List<Module> modules = new List<Module>();
			
			String sql = "select id, name, status, credit, addDate from t_module ";
			
			SQLiteDataReader sdr = helper.Read(sql);
			
			while (sdr.Read()) 
			{
				Module retModule = new Module();
				retModule.id = sdr.GetInt32(0);
				retModule.name = sdr.GetString(1);
				retModule.status = sdr.GetInt32(2);
				retModule.credit = sdr.GetInt32(3);
				retModule.addDate = sdr.GetString(4);
				
				modules.Add(retModule);
				
			}
			
			sdr.Close();
			sdr.Dispose();
			
			
			return modules;
		}

        public List<Module> queryNotSelected(int programmeId)
        {
            List<Module> modules = new List<Module>();

            String sql = "select id, name, status, credit, addDate from t_module where id not in (select moduleId from t_programme_module where programmeId = '"+programmeId+"') ";

            SQLiteDataReader sdr = helper.Read(sql);

            while (sdr.Read())
            {
                Module retModule = new Module();
                retModule.id = sdr.GetInt32(0);
                retModule.name = sdr.GetString(1);
                retModule.status = sdr.GetInt32(2);
                retModule.credit = sdr.GetInt32(3);
                retModule.addDate = sdr.GetString(4);

                modules.Add(retModule);

            }

            sdr.Close();
            sdr.Dispose();


            return modules;
        }


        public List<Module> querySelected(int programmeId)
        {
            List<Module> modules = new List<Module>();

            String sql = "select id, name, status, credit, addDate from t_module where id in (select moduleId from t_programme_module where programmeId = '" + programmeId + "') ";

            SQLiteDataReader sdr = helper.Read(sql);

            while (sdr.Read())
            {
                Module retModule = new Module();
                retModule.id = sdr.GetInt32(0);
                retModule.name = sdr.GetString(1);
                retModule.status = sdr.GetInt32(2);
                retModule.credit = sdr.GetInt32(3);
                retModule.addDate = sdr.GetString(4);

                modules.Add(retModule);

            }

            sdr.Close();
            sdr.Dispose();


            return modules;
        }

		#endregion


        public Module queryByName(string name)
        {
            String sql = "select id, name, status, credit, addDate from t_module where name = @name";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",name)
			};

            SQLiteDataReader sdr = helper.Read(sql, param);

            Module retModule = new Module();

            while (sdr.Read())
            {
                retModule.id = sdr.GetInt32(0);
                retModule.name = sdr.GetString(1);
                retModule.status = sdr.GetInt32(2);
                retModule.credit = sdr.GetInt32(3);
                retModule.addDate = sdr.GetString(4);

            }

            sdr.Close();
            sdr.Dispose();


            return retModule;
        }
        
        
        public SQLiteDataAdapter getDisplayColumn(String sql)
		{        	
        	SQLiteDataAdapter da = helper.QueryDA(sql);
        	
        	return da;
		}
    }
}
